﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BI.CF.Model;
using System.Data;
using System.Data.SqlClient;
using BI.CF.IDAL;
using System.Configuration;


namespace BI.CF.DAL
{
    public class BulletinDAL:BulletinIDAL
    {
        public static string connectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;

        /// <summary>
        /// 广告标题显示
        /// </summary>
        /// <returns></returns>
        public List<BulletinModel> BulletinShowOne(int tid)
        {

            SqlParameter[] paremeter ={
                                    new  SqlParameter("@Tid",SqlDbType.Int),
                                    };
            paremeter[0].Value = tid;
            SqlDataReader reader = DbHelperSQL.RunProcedure("Proc_BulletinSelect", paremeter);
           List<BulletinModel> list = new List<BulletinModel>();
           while (reader.Read())
           {
               BulletinModel Model = new BulletinModel();
               Model.BulletinID = Convert.ToInt32(reader["BulletinID"]);
               Model.B_Tile = reader["B_Tile"].ToString();
               Model.PT_Name = reader["PT_Name"].ToString();
               Model.URL = reader["URL"].ToString();
               list.Add(Model);
           }
           return list;
        }
        /// <summary>
        /// 广告详细信息
        /// </summary>
        /// <returns></returns>
        public BulletinModel BulletinContentShow(int id)
        {
            SqlParameter[] paremeter = {
                                       new SqlParameter("@BulletinID",SqlDbType.Int)
                                       };
            paremeter[0].Value = id;
            SqlDataReader reader = DbHelperSQL.RunProcedure("Proc_BulletinSelectContent",paremeter);
            reader.Read();
                BulletinModel Model = new BulletinModel();
                Model.B_Tile = reader["B_Tile"].ToString();
                Model.Publish_Content = reader["Publish_Content"].ToString();
                Model.Publish_Time = Convert.ToDateTime(reader["Publish_Time"]).ToString("yyyy-MM-dd");
                Model.PV = Convert.ToInt32(reader["PV"]);
                Model.PT_Name = reader["PT_Name"].ToString();
                Model.TID =Convert.ToInt32(reader["TID"]);
                return Model;
        }
        /// <summary>
        /// 根据标题名称查询广告信息ID
        /// </summary>
        /// <returns></returns>
        public int BulletinIDshow(string name)
        {
            SqlParameter[] paremeter1 ={
                                    new SqlParameter("@B_Tile",SqlDbType.VarChar)
                                   };
            paremeter1[0].Value = name;
            SqlDataReader reader1 = DbHelperSQL.RunProcedure("Proc_BulletinSelectBulletinID", paremeter1);
            reader1.Read();
            BulletinModel Model1 = new BulletinModel();
            Model1.BulletinID = Convert.ToInt32(reader1["BulletinID"]);
            int i=Model1.BulletinID;
            return i;
        }
        /// <summary>
        /// 修改浏览量
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool BulletinUpdatePV(int id)
        {
            SqlParameter[] paremeter ={
                                    new SqlParameter("@BulletinID",SqlDbType.Int)
                                   };
            paremeter[0].Value = id;
            return DbHelperSQL.RunProcedureReturnBool("Proc_BulletinUpdatePV", paremeter);
        }



        //后台数据管理

        /// <summary>
        /// 广告类别显示
        /// </summary>
        /// <returns></returns>
        public List<PublishTypeModel> PublishTypeShow()
        {
            SqlDataReader reader = DbHelperSQL.RunProcedurenull("Proc_PublishTypeSelect");
            List<PublishTypeModel> list = new List<PublishTypeModel>();
            while (reader.Read())
            {
                PublishTypeModel model = new PublishTypeModel();
                model.PT_Name = reader["PT_Name"].ToString();
                model.TID = Convert.ToInt32(reader["TID"]);
                list.Add(model);
            }
            return list;
        }
        /// <summary>
        /// 广告类型删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool PublishTypeDelete(int id)
        {
            SqlParameter[] paraneter ={
                                    new SqlParameter("@TID",SqlDbType.Int)
                                   };
            paraneter[0].Value = id;
            return DbHelperSQL.RunProcedureReturnBool("Proc_PublishTypeDelete", paraneter);
        }
        /// <summary>
        /// 修改反填新闻类别信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public PublishTypeModel fantian(int id)
        { 
            SqlParameter [] paraneter={
                                       new SqlParameter("@TID",SqlDbType.Int)
                                      };
            paraneter[0].Value = id;
            SqlDataReader reader = DbHelperSQL.RunProcedure("Proc_PublishTypeIDSelect", paraneter);
            PublishTypeModel model = new PublishTypeModel();
            reader.Read();
            model.TID = Convert.ToInt32(reader["TID"]);
            model.PT_Name = reader["PT_Name"].ToString();
            return model;
        }

        /// <summary>
        /// 修改广告类型
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool PublishTypeUpdate(PublishTypeModel upd)
        {
            
            SqlParameter[] paraneter ={
                                    new SqlParameter("@TID",SqlDbType.Int),
                                    new SqlParameter("@PT_Name",SqlDbType.VarChar),
                                   };
            paraneter[0].Value = upd.TID;
            paraneter[1].Value = upd.PT_Name;
            return DbHelperSQL.RunProcedureReturnBool("Proc_PublishTypeUpdate", paraneter);
        }
        /// <summary>
        /// 修改广告详细信息
        /// </summary>
        /// <param name="upd"></param>
        /// <returns></returns>
        public bool BulletinUpdate(BulletinModel upd)
        {
            SqlParameter[] paraneter ={
                                      new SqlParameter("@BulletinID",SqlDbType.Int),
                                      new SqlParameter("@B_Tile",SqlDbType.VarChar),
                                      new SqlParameter("@Publish_Time",SqlDbType.VarChar),
                                      new SqlParameter("@PV",SqlDbType.Int),
                                      new SqlParameter("@Publish_Content",SqlDbType.VarChar),
                                      new SqlParameter("@TID",SqlDbType.Int),
                                      };
            paraneter[0].Value = upd.BulletinID;
            paraneter[1].Value = upd.B_Tile;
            paraneter[2].Value = upd.Publish_Time;
            paraneter[3].Value = upd.PV;
            paraneter[4].Value = upd.Publish_Content;
            paraneter[5].Value = upd.TID;
            return DbHelperSQL.RunProcedureReturnBool("Proc_UpdateBulletin", paraneter);
        }
        /// <summary>
        /// 广告详细信息显示
        /// </summary>
        /// <returns></returns>
        public List<BulletinModel> BulletinShowAll(int pageSize,int pageIndex,string name)
        {
            SqlParameter[] Parameter ={
                                      new SqlParameter("@PageSize",SqlDbType.Int),
                                      new SqlParameter("@pageIndex",SqlDbType.Int),
                                      new SqlParameter("@B_Tile",SqlDbType.VarChar)
                                      };
            Parameter[0].Value = pageSize;
            Parameter[1].Value = pageIndex;
            Parameter[2].Value = name;
            SqlDataReader reader = DbHelperSQL.RunProcedure("Proc_BulletinSelectAll",Parameter);
            List<BulletinModel> list = new List<BulletinModel>();
            while (reader.Read())
            {
                BulletinModel mdoel = new BulletinModel();
                mdoel.B_Tile = reader["B_Tile"].ToString();
                mdoel.BulletinID = Convert.ToInt32(reader["BulletinID"]);
                mdoel.Publish_Time = reader["Publish_Time"].ToString();
                mdoel.PT_Name = reader["PT_Name"].ToString();
                mdoel.PV = Convert.ToInt32(reader["PV"]);
                mdoel.Publish_Content = reader["Publish_Content"].ToString();
                list.Add(mdoel);
            }
            return list;
        }
        /// <summary>
        /// 查询广告类型总条数
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public int BulletinCount(string name)
        {
            SqlParameter[] Parameter ={
                                    new SqlParameter("@B_Tile",SqlDbType.VarChar)
                                   };
            Parameter[0].Value = name;
            return Convert.ToInt32(DbHelperSQL.RunProcedureReturnSingle("Proc_BulletinSelectCount",Parameter));
        }




        public List<BulletinModel> BulletinShowTwo()
        {
            throw new NotImplementedException();
        }

        public List<BulletinModel> BulletinShowThree()
        {
            throw new NotImplementedException();
        }

        public List<BulletinModel> BulletinShowFour()
        {
            throw new NotImplementedException();
        }

        public BulletinModel Bullid(int id)
        {
            //SqlConnection conn = new SqlConnection("Data Source=DESKTOP-JGQ6TMD;Initial Catalog=CF_DB;User ID=sa;pwd=123;");
            

            SqlConnection conn = new SqlConnection(connectionString);

            string sql = "select * from Bulletin inner join PublishType on Bulletin.TID=PublishType.TID WHERE BulletinID=" + id + "";

            SqlCommand comm = new SqlCommand(sql, conn);
            conn.Open();
            SqlDataReader reader = comm.ExecuteReader();
            BulletinModel mdoel = new BulletinModel();
            while (reader.Read())
            {
                mdoel.B_Tile = reader["B_Tile"].ToString();
                mdoel.BulletinID = Convert.ToInt32(reader["BulletinID"]);
                mdoel.Publish_Time = reader["Publish_Time"].ToString();
                mdoel.PV = Convert.ToInt32(reader["PV"]);
                mdoel.PT_Name = reader["PT_Name"].ToString();
                mdoel.Publish_Content = reader["Publish_Content"].ToString();
            }
            conn.Close();
            return mdoel;
        }

        public bool BullUpt(BulletinModel upd)
        {

            SqlParameter[] paraneter ={
                                    new SqlParameter("@BulletinID",SqlDbType.Int),
                                    new SqlParameter("@B_Tile",SqlDbType.VarChar),
                                   };
            paraneter[0].Value = upd.BulletinID;
            paraneter[1].Value = upd.B_Tile;
            return DbHelperSQL.RunProcedureReturnBool("Proc_BullUpt", paraneter);
        }

    }
}
